package com.salinity.kun.helper;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.salinity.kun.algorithm.harmonic.ConstantParamHelper;
import com.salinity.kun.model.StationDateData;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

public class OracleHelper {
	PreparedStatement pre = null;
	private Connection conn = null;

	public Connection prepareConection() {
		if (conn == null) {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(ConstantHelper.getOracleUrl(), ConstantHelper.getOracleUserName(),
						ConstantHelper.getOraclePassword());
			} catch (ClassNotFoundException | SQLException e) {
				e.printStackTrace();
			}
		}
		return conn;
	}

	public PreparedStatement prepareTideSQL() throws SQLException {
		String sql = "select CLSJ,CLSW from T_SZHD_SWDTSLXX where (CLSJ between ? and ?) and SWZID = ? order by CLSJ";
		if (conn == null) {
			prepareConection();
		}

		if (pre == null) {

			pre = conn.prepareStatement(sql);
		}
		return pre;
	}
	

	public ResultSet doTideQuery(Date SDate, Date EDate, String stationID) throws SQLException {
		pre.setTime(1, new Time(SDate.getTime()));
		pre.setTime(2, new Time(EDate.getTime()));
		pre.setString(3, stationID);
		ResultSet rslt = pre.executeQuery();
		return rslt;

	}
	
	public PreparedStatement prepareFluxSQL() throws SQLException {
		String sql = "select SJ,RKLL from T_SZHD_RGSWLLXX_NOW where (SJ between ? and ?) order by SJ";
		
		if (conn == null) {
			prepareConection();
		}

		if (pre == null) {

			pre = conn.prepareStatement(sql);
		}
		return pre;
		
	}
	
	public ResultSet doFluxQuery(Date SDate, Date EDate) throws SQLException {
		pre.setTime(1, new Time(SDate.getTime()));
		pre.setTime(2, new Time(EDate.getTime()));
		ResultSet rslt = pre.executeQuery();
		return rslt;
	}

	public List<StationDateData> parseRslt(ResultSet rsltSet) throws SQLException {
		SimpleDateFormat sdf = ConstantHelper.getDefaultFormat();

		return parseRslt(rsltSet, sdf);
	}

	public List<StationDateData> parseRslt(ResultSet rsltSet, SimpleDateFormat sdf) throws SQLException {
		List<StationDateData> rsltList = new ArrayList<>();
		DecimalFormat df = ConstantParamHelper.getDefaultDecimalFormat();
		while (rsltSet.next()) {
			rsltList.add(new StationDateData(null, sdf.format(rsltSet.getTimestamp(1)), Double.valueOf(df.format(rsltSet.getDouble(2)))));
		}

		return rsltList;
	}

	public static ResultSet excuteSql(Connection conn, String sql) throws SQLException {
		PreparedStatement pre = conn.prepareStatement(sql);
		ResultSet resultSet = pre.executeQuery();
		pre.close();
		return resultSet;
	}

	public static ResultSet excuteSql(Connection conn, PreparedStatement pre) throws SQLException {
		ResultSet resultSet = pre.executeQuery();
		return resultSet;
	}

	/**
	 * 利用的id 来进行去重操作
	 * @param conn
	 * @param tableName
	 * @throws SQLException
	 */
	public static void deduplication(Connection conn, String tableName) throws SQLException {
		// 更快的做法
		String sql = "DELETE from " + tableName
				+ " WHERE rowid IN (select rowid  from (select id as s1, lag(id,1,0) over(order by id asc) as s2 from "
				+ tableName + " ) where s1 = s2)";

		OracleHelper.excuteSql(conn, sql);
	}

	public void end() throws SQLException {
		if (pre != null) {
			pre.close();
		}
		if (conn != null) {
			conn.close();
		}
	}
}
